﻿<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Bieb.Domain"
                   namespace="Bieb.Domain.Entities">

  <class name="Book"> 
    
    <id name="Id" column="BookId">
      <generator class="native" />
    </id>
    
    <version name="ModifiedDate" unsaved-value="null" generated="always" type="DateTime2">
      <column name="ModifiedDate" not-null="false" sql-type="DATETIME2" />
    </version>
    <property name="CreatedDate" generated="always" type="DateTime2">
      <column name="CreatedDate" not-null="false" sql-type="DATETIME2" />
    </property>
    
    <property name="Title" index="IX_Book_Title" not-null="true" />
    <property name="TitleSort" column="TitleSort" generated="always" access="field.camelcase" />
    <property name="Subtitle" />
    <property name="LibraryStatus" not-null="true" />
    <property name="Isbn" />
    <property name="IsbnLanguage" access="field.camelcase" />
    <property name="Year" />

    <many-to-one name="Publisher" column="PublisherId" foreign-key="FK_Book_Publisher" />

    <map name="Stories" table="Story">
      <key column="BookId" foreign-key="FK_Story_Book" />
      <index column="PositionInBook" type="Int32" />
      <one-to-many class="Story" />
    </map>

    <set name="Editors" table="BookEditor" access="field.camelcase">
      <key column="BookId" foreign-key="FK_BookEditor_Book" />
      <many-to-many column="PersonId" class="Person" foreign-key="FK_BookEditor_Person" />
    </set>

    <set name="Authors" table="BookAuthor" access="field.camelcase">
      <key column="BookId" foreign-key="FK_BookAuthor_Book" />
      <many-to-many column="PersonId" class="Person" foreign-key="FK_BookAuthor_Person" />
    </set>

    <set name="Translators" table="BookTranslator" access="field.camelcase">
      <key column="BookId" foreign-key="FK_BookTranslator_Book" />
      <many-to-many column="PersonId" class="Person" foreign-key="FK_BookTranslator_Person" />
    </set>

    <bag name="DbSeries" table="SeriesBook" access="field.camelcase">
      <key column="BookId" foreign-key="FK_SeriesBook_Book" />
      <many-to-many column="SeriesId" class="Series" foreign-key="FK_SeriesBook_Series" />
    </bag>

    <set name="Tags" table="BookTag" access="field.camelcase">
      <key column="BookId" foreign-key="FK_BookTag_Story" />
      <many-to-many column="TagId" class="Tag" foreign-key="FK_BookTag_Tag" />
    </set>

    <!-- Fully qualified name on the generic type needed, see: http://stackoverflow.com/q/12547084/419956 -->
    <bag name="Reviews" table="Review" access="field.camelcase">
      <key column="BookId" foreign-key="FK_Review_Book" />
      <one-to-many class="Review`1[Bieb.Domain.Entities.Book, Bieb.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]" />
    </bag>

    <many-to-one name="ReferenceBook" column="ReferenceBookId" foreign-key="FK_Story_ReferenceBook" />

    <bag name="ReferencedByBooks" access="field.camelcase">
      <key column="ReferenceBookId" foreign-key="FK_Story_ReferenceBook" />
      <one-to-many class="Book" />
    </bag>
       
  </class>

  <database-object>
    <create>
      ALTER TABLE Book DROP COLUMN TitleSort
      GO

      ALTER TABLE Book
      ADD TitleSort AS (
        CASE
          WHEN LEFT(Title, 3) = 'De '  AND LEN(Title) >= 3 THEN RIGHT(Title, LEN(Title)-3) + ', De'
          WHEN LEFT(Title, 4) = 'Het ' AND LEN(Title) >= 4 THEN RIGHT(Title, LEN(Title)-4) + ', Het'
          WHEN LEFT(Title, 4) = 'Een ' AND LEN(Title) >= 4 THEN RIGHT(Title, LEN(Title)-4) + ', Een'
          WHEN LEFT(Title, 4) = 'The ' AND LEN(Title) >= 4 THEN RIGHT(Title, LEN(Title)-4) + ', The'
          WHEN LEFT(Title, 2) = 'A '   AND LEN(Title) >= 2 THEN RIGHT(Title, LEN(Title)-2) + ', A'
          ELSE Title
        END
      ) PERSISTED
    </create>
    <drop>      
    </drop>
  </database-object>

  <database-object>
    <create>
      GO
      CREATE TRIGGER TR_BookInserted ON Book AFTER INSERT AS
      BEGIN
        SET NOCOUNT ON
        
        UPDATE Book 
        SET    ModifiedDate = GetDate(), CreatedDate = GetDate() 
        FROM   inserted 
        WHERE  Book.BookId = inserted.BookId
        
        SET NOCOUNT OFF
      END
      GO
      CREATE TRIGGER TR_BookUpdated ON Book AFTER UPDATE AS
      BEGIN
        SET NOCOUNT ON
        
        UPDATE Book 
        SET    ModifiedDate = GetDate() 
        FROM   inserted 
        WHERE  Book.BookId = inserted.BookId
        
        SET NOCOUNT OFF
      END
      GO
    </create>
    <drop>
    </drop>
  </database-object>

  <database-object>
    <create>
      ALTER TABLE Book
      ADD CONSTRAINT CT_OnlyReferenceBookNeedsNoTitle
      CHECK (LibraryStatus = 0 OR Title IS NOT NULL)
    </create>
    <drop>
    </drop>
  </database-object>
  
</hibernate-mapping>